#! /usr/bin/env python
# _*_ coding:UTF-8 _*_
"""
@author:zhh
@Software:PyCharm
@blogs:https://blog.csdn.net/smilehaoh
@time:2020/4/1

"""
from common.connect_db import MyDB

my_db = MyDB()


class Storeassets:
    @staticmethod
    def get_old_storeassets():
        """
        查询老系统线上线下商家的总资产
        :return:
        """
        sql1 = "SELECT SUM(t.balance) as totalbalance from (SELECT s.mid,CONVERT(SUBSTRING(SUM(IFNULL(a.amount,0))* (1 - s.rate/100),1,INSTR(ROUND(SUM(IFNULL(a.amount,0))* (1 - s.rate/100),3),'.')+2),DECIMAL(20,2)) AS  balance," \
               "s.mid as new_storeid FROM ims_ewei_shop_store s left JOIN ims_ewei_shop_store_account a ON s.id = a.storeid where s.mid > 0 and s.deleted = 0 GROUP BY s.mid ORDER BY s.mid) t"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_withamount():
        """
        查询老系统线上线下商家的总可提现金额
        :return:
        """
        #  sql ="SELECT SUM(t.balance) as totalbalance FROM (SELECT s.mid,CONVERT(SUBSTRING(ROUND(SUM(IFNULL(a.amount,0))* (1 - s.rate/100),3),1,INSTR(ROUND(SUM(IFNULL(a.amount,0))* (1 - s.rate/100),3),'.')+2),DECIMAL(20,2)) AS  balance,s.mid as new_storeid FROM ims_ewei_shop_store s left JOIN ims_ewei_shop_store_account a ON s.id = a.storeid where s.mid > 0 and s.deleted = 0 and storeid not in (select id from ims_ewei_shop_store GROUP BY mid HAVING count(mid)>1) GROUP BY s.mid HAVING count(s.mid) = 1  ORDER BY balance desc) t where t.balance>0"
        sql = "SELECT SUM(t.balance) as totalbalance from (SELECT s.mid,CONVERT(SUBSTRING(SUM(IFNULL(a.amount,0))* (1 - s.rate/100),1,INSTR(ROUND(SUM(IFNULL(a.amount,0))* (1 - s.rate/100),3),'.')+2),DECIMAL(20,2)) AS  balance,s.mid as new_storeid FROM ims_ewei_shop_store s left JOIN ims_ewei_shop_store_account a ON s.id = a.storeid where s.mid > 0 and s.deleted = 0 and storeid not in (select id from ims_ewei_shop_store GROUP BY mid HAVING count(mid)>1) GROUP BY s.mid HAVING count(s.mid) = 1  ORDER BY balance desc) t where t.balance>0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_agentamount():
        """
        查询老系统代理商可提现总金额
        :return:
        """
        sql = "select sum(surplus_commission) as totalamount from ims_ewei_shop_agent "
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_agentamount1():
        """
        查询老系统代理商可提现金额
        :return:
        """
        sql = "select surplus_commission as totalamount from ims_ewei_shop_agent "
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_storeassets():
        """
        查询新系统商家的总资产
        1 (会员余额) 2 (线上线下) 3 (供应商) 4 (代理商) 5 (油卡商) 6 (创客)
        :return:
        """
        sql1 = "select sum(balance) as balance from finance_account  where account_type=2"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_storeassets1():
        """
        查询新系统商家的总资产
        1 (会员余额) 2 (线上线下) 3 (供应商) 4 (代理商) 5 (油卡商) 6 (创客)
        :return:
        """
        sql1 = "select balance as amount from finance_account  where account_type=2"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_agentamount():
        """
        查询新系统代理商的总可提现资产
        1 (会员余额) 2 (线上线下) 3 (供应商) 4 (代理商) 5 (油卡商) 6 (创客)
        :return:
        """
        sql1 = "select sum(balance) as totalamount from finance_account  where account_type=4"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_agentamount1():
        """
        查询新系统代理商的总可提现资产
        1 (会员余额) 2 (线上线下) 3 (供应商) 4 (代理商) 5 (油卡商) 6 (创客)
        :return:
        """
        sql1 = "select balance as totalamount from finance_account  where account_type=4"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def storeverify():
        """
        新系统店铺id不为0情况验证
        """
        sql1 = "select * from store_stores where member_id=0 or member_id!=id"
        result = my_db.execute_select(select_db='new', db_name='store', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            print("店铺数据验证正确")

    @staticmethod
    def creditsverify():
        """
        新系统积分不为0情况验证
        """
        sql1 = "select * from common_coin_credits where id!=member_id and account_type=1 or id=0 or member_id=0"
        result = my_db.execute_select(select_db='new', db_name='coin', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            print("积分数据验证正确")

    @staticmethod
    def coinverify():
        """
        新系统微币不为0情况验证
        """
        sql1 = "select * from common_coin_coin where id!=member_id  and account_type=1 or id=0 or member_id=0"
        result = my_db.execute_select(select_db='new', db_name='coin', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            print("微币数据验证正确")

    @staticmethod
    def financeverify():
        """
        新系统资金id不为0情况验证
        """
        sql1 = "select * from finance_account where id=0"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            print("资金数据验证正确")

    @staticmethod
    def get_old_agentfinance():
        """
        查询老系统代理商总已提现资产
        :return:
        """
        sql = "SELECT sum(total_commission-surplus_commission) as agentfinane FROM ims_ewei_shop_agent"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_agentfinance():
        """
        新系统代理商总已提现资产
        """
        sql1 = "select sum(money_real) as after_balabce from withdraw_order where  account_type=4"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_storefinance():
        """
        查询老系统线上线下商家总已提现资产
        :return:
        """
        sql = "select sum(t.money_real) as after_balabce from (select   ROUND(IFNULL(l.money,0),2) AS money_real FROM ims_ewei_shop_store s left JOIN ims_ewei_shop_store_account a on s.id = a.storeid left join ims_ewei_shop_store_log l on l.storeid = a.storeid LEFT JOIN ims_ewei_shop_member m ON s.mid = m.id WHERE l.`status` in(2) and s.deleted = 0) t"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_storerate():
        """
        查询老系统线上线下商家总手续费
        :return:
        """
        sql = "select sum(t.commission) as  fee from (SELECT ROUND(IFNULL(l.rate,0),2) AS commission FROM ims_ewei_shop_store s left JOIN ims_ewei_shop_store_account a on s.id = a.storeid left join ims_ewei_shop_store_log l on l.storeid = a.storeid LEFT JOIN ims_ewei_shop_member m ON s.mid = m.id WHERE l.`status` in(2) and s.deleted = 0) t"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_storefinance():
        """
        新系统线上线下商家总已提现资产
        """
        sql1 = "select sum(money_real) as after_balabce from withdraw_order where  account_type=2"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_storefinance1():
        """
        新系统线上线下商家总已提现资产
        """
        sql1 = "select after_balance as afterbalance from finance_account_log where account_id in (select id from finance_account where account_type=2) and transaction_type=23"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_storerate():
        """
        新系统线上线下商家总手续费
        """
        sql1 = "select sum(commission) as fee from withdraw_order where account_type=2"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_accountid():
        """
        获取未合并会员的总可提现金额
        """
        sql1 = "select mid from ims_ewei_shop_store GROUP BY mid HAVING count(mid)>1 "
        result = my_db.execute_select(select_db='old', db_name='quanfan', sql_text=sql1)
        list = []
        for item in result:
            a = item[0]
            list.append(a)
        b = ','.join('%s' % id for id in list)
        sql2 = "select sum(balance) as totalbalance from finance_account where id  not in ('%s') and account_type=2 and balance>0 " % b
        result1 = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql2)
        if len(result1) > 0:
            return result1
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_accountid1():
        """
        #获取新系统重复的资金id
        """
        sql1 = "select mid from ims_ewei_shop_store GROUP BY mid HAVING count(mid)>1 "
        result = my_db.execute_select(select_db='old', db_name='quanfan', sql_text=sql1)
        list = []
        for item in result:
            a = item[0]
            list.append(a)
        b = ','.join('%s' % id for id in list)
        sql2 = "select sum(balance) as amount from finance_account where id  in ('%s') and account_type=2" % b
        result1 = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql2)
        if len(result1) > 0:
            return result1
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_withdrawrecord():
        """
        查询老系统会员提现申请中应该为0
        :return:
        """
        # sql1 = "select count(*) as aa from ims_ewei_shop_member_log where status in (-1,2) and type=1"
        sql1 = "select count(*) as aa from ims_ewei_shop_member_log where status in (1,0) and type=1"
        result = my_db.execute_select(select_db='old', db_name='quanfan', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_withdrawrecords():
        """
        查询老系统会员提现记录
        :return:
        """
        sql1 = "select  count(*) as aa from ims_ewei_shop_member_log where status in (-1,2) and type=1"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_swithdrawrecord():
        """
        查询老系统商家提现记录为0
        :return:
        """
        sql1 = "select count(*) as hh from ims_ewei_shop_store_log where status in (1,3)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_shopwithdrawrecord():
        """
        查询老系统商家提现总记录
        :return:
        """
        sql1 = "select count(t.money_real) as after_balabce from (select   ROUND(IFNULL(l.money,0),2) AS money_real FROM ims_ewei_shop_store s left JOIN ims_ewei_shop_store_account a on s.id = a.storeid left join ims_ewei_shop_store_log l on l.storeid = a.storeid LEFT JOIN ims_ewei_shop_member m ON s.mid = m.id WHERE l.`status` in(2) and s.deleted = 0) t"
        result = my_db.execute_select1(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_mwithdrawrecords():
        """
        新系统会员提现记录
        """
        sql1 = "select  count(*) as newrecord from common_coin_coin_log where child_type in ('DC','CL') and coin_id in (select id from common_coin_coin where account_type=1)"
        result = my_db.execute_select(select_db='new', db_name='coin', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_swithdrawrecords():
        """
        新系统商家提现记录总数
        """
        sql1 = "select  count(money_real)  as newrecords from withdraw_order where account_type=2"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_withdrawinvestrecord():
        """
        查询老系统投资提现记录
        :return:
        """
        sql1 = "select count(*) as investrecord from ims_ewei_invest_member_log where status=1"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_withdrawinvestrecords():
        """
        查询老系统投资提现记录
        :return:
        """
        sql1 = "select count(id) from ims_ewei_invest_member_log where  mid in (select id from ims_ewei_shop_member where mobile regexp '[^0-9.]'=0) and status in (-1,2)"
        result = my_db.execute_select1(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_iwithdrawrecords():
        """
        新系统商家提现记录
        """
        sql1 = "select  count(*) from common_coin_coin_log where child_type in ('DC','CL') and coin_id in (select id from common_coin_coin where account_type=2)"
        result = my_db.execute_select(select_db='new', db_name='coin', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_agentwithdrawrecord():
        """
        查询老系统代理商提现记录
        :return:
        """
        sql1 = "select count(t.money_real) as totalcount from (SELECT withdraw_amount as money_real FROM ims_ewei_shop_agent ag join ims_ewei_shop_agent_apply ap  ON ap.agent_id = ag.areaid) t"
        result = my_db.execute_select1(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_agentrecord():
        """
        新系统代理商提现记录
        """
        sql1 = "select  count(money_real)  as newrecords from withdraw_order where account_type=4"
        result = my_db.execute_select(select_db='new', db_name='finance', sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_llnull():
        """
        查询老系统代理商提现没有申请中的记录
        :return:
        """
        # sql1 ="select count(*) from ims_ewei_shop_agent_apply where withdraw_status=1"
        sql1 = "select count(*) from ims_ewei_shop_agent_apply where withdraw_status=1"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")


if __name__ == '__main__':
    storeassets = Storeassets()
    oldwithdrawrecords = storeassets.get_old_withdrawinvestrecord()
#  new = storeassets.get_llnull()
